package com.example.cet4try.Utils;

import android.util.Log;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

public class MySQLUtils {

//    private static String url = "jdbc:mysql://localhost:3306/map_designer_test_db";

    public static Connection openConnection() {
        Connection conn = null;

        String ip = "39.97.177.98";
        String user = "yz";
        String password = "YtTr7p683dHcMFNK";
        String dbName = "user";

        try {
            final String DRIVER_NAME = "com.mysql.jdbc.Driver";
            Class.forName(DRIVER_NAME);
            conn = DriverManager.getConnection("jdbc:mysql://" + ip + ":3306/" + dbName,user, password);
        } catch (ClassNotFoundException | SQLException ignored) {

        }

        return conn;
    }

    public static boolean execSQL(String sql) {

        Connection conn = openConnection();
        //insert into users(username,password) values("lbm","123456")
        boolean execResult = false;
        if (conn == null) {
            return false;
        }

        Statement statement = null;

        try {
            statement = conn.createStatement();
            if (statement != null) {
                statement.execute(sql);
                execResult = true;
            }
            conn.close();
        } catch (SQLException ignored) {

        }

        return execResult;
    }

    public static boolean AddUser(String username,String nickname,String password){
        //insert into users(username,nickname,password) values('lbm','lbm','123456')
        if (nickname.length() < 1)
            nickname = username;
        String sql = "insert into users(username,nickname,password) values('"+username+"','"+nickname+"','"+password+"')";
        return execSQL(sql);
    }

    public static boolean CheckUser(String name){
        HashMap<String, Object> map = SelectUser(name);
        if(map != null){
            String p = (String) map.get("password");
            if (p != null)
                return true;
        }
        return false;
    }

    public static HashMap<String, Object> SelectUser(String name){

        HashMap<String, Object> map = new HashMap<>();
//        String res = null;
        // 根据数据库名称，建立连接
        Connection connection = openConnection();

        try {
            // mysql简单的查询语句。这里是根据MD_CHARGER表的NAME字段来查询某条记录
            String sql = "select * from users where username = ?";
            if (connection != null){// connection不为null表示与数据库建立了连接
                PreparedStatement ps = connection.prepareStatement(sql);
                if (ps != null){
                    // 设置上面的sql语句中的？的值为name
                    ps.setString(1, name);
                    // 执行sql查询语句并返回结果集
                    ResultSet rs = ps.executeQuery();
                    if (rs != null){
                        int count = rs.getMetaData().getColumnCount();
                        Log.e("DBUtils","列总数：" + count);
                        while (rs.next()){
                            // 注意：下标是从1开始的
                            for (int i = 1;i <= count;i++){
                                String field = rs.getMetaData().getColumnName(i);
                                map.put(field, rs.getString(field));
                            }
                        }
                        connection.close();
                        ps.close();
                        return  map;
                    }else {
                        return null;
                    }
                }else {
                    return  null;
                }
            }else {
                return  null;
            }
        }catch (Exception e){
            e.printStackTrace();
            Log.e("DBUtils","异常：" + e.getMessage());
            return null;
        }

    }

    public static void getAllData(String table,ArrayList<String> data){
        //HashMap<String, Object> map = new HashMap<>();
//        String res = null;
        // 根据数据库名称，建立连接
        Connection connection = openConnection();

        try {
            // mysql简单的查询语句。这里是根据MD_CHARGER表的NAME字段来查询某条记录
            String sql = "select * from "+table;
            if (connection != null){// connection不为null表示与数据库建立了连接
                PreparedStatement ps = connection.prepareStatement(sql);
                if (ps != null){
                    // 设置上面的sql语句中的？的值为name
                    //ps.setString(1, name);
                    // 执行sql查询语句并返回结果集
                    ResultSet rs = ps.executeQuery();
                    if (rs != null){
                        int count = rs.getMetaData().getColumnCount();
                        //Log.e("DBUtils","列总数：" + count);
                        while (rs.next()){
                            // 注意：下标是从1开始的
                            for (int i = 1;i <= count;i++){
                                data.add(rs.getString(i));
                                //String field = rs.getMetaData().getColumnName(i);
                                //map.put(field, rs.getString(field));
                            }
                        }
                        connection.close();
                        ps.close();
                        //return  map;
                    } //return null;

                }
            }
        }catch (Exception e){
            e.printStackTrace();
            //Log.e("DBUtils","异常：" + e.getMessage());
            //return null;
        }

    }

    public static String getInternetTime() {
        Connection connection = openConnection();
        try {
            // mysql简单的查询语句。这里是根据MD_CHARGER表的NAME字段来查询某条记录
            String sql = "select now()";
            if (connection != null){// connection不为null表示与数据库建立了连接
                PreparedStatement ps = connection.prepareStatement(sql);
                if (ps != null){
                    // 执行sql查询语句并返回结果集
                    ResultSet rs = ps.executeQuery();
                    if (rs != null){
                        String time = null;
                        if (rs.next()){
                            String data = rs.getMetaData().getColumnName(1);
                            time = rs.getString(data);
                        }
                        connection.close();
                        ps.close();
                        return  time;
                    }else {
                        return null;
                    }
                }else {
                    return  null;
                }
            }else {
                return  null;
            }
        }catch (Exception e){
            e.printStackTrace();
            Log.e("DBUtils","异常：" + e.getMessage());
            return null;
        }
    }

    public static boolean submitFeedback(Map<String,String> feedback){
        String sql = "insert into feedback(time,content,user,nick) values('"+ feedback.get("time")+"','"+feedback.get("content")+"','"+feedback.get("user")+"','"+feedback.get("nick")+"')";
        return execSQL(sql);
    }

    public static boolean updateData(String table, String set,String where){
        String sql = "UPDATE "+table+" SET "+set+" "+where;
        return execSQL(sql);
    }

    public static String toUpdateSet(String[] keys,String[] values){
        StringBuilder res = new StringBuilder();
        for (int i = 0;i<keys.length;i++){
            if (i!=0)
                res.append(",");
            res.append(keys[i]).append("=").append(values[i]);
        }
        return res.toString();
    }


}
